package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class LabTestListDAO {
	private static final String CREATE_LAB_TEST_LIST = "INSERT INTO qdlc_lab_test_list (labTestRecordId, labTestId) VALUES (?,?)";
	private static final String UPDATE_LAB_TEST_LIST = "UPDATE qdlc_lab_test_list SET labTestRecordId = ?, labTestId WHERE labTestListId = ?";
	private static final String GET_LAB_TEST_LIST_FROM_ID = "SELECT * FROM qdlc_lab_test_list WHERE labTestRecordId = ?;";
	private static final String GET_ALL_LAB_TEST_LIST = "SELECT * FROM qdlc_lab_test_list;";

	public LabTestList createLabTestList(int labTestRecordId, int labTestId) {
		LabTestList list = null;

		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn.prepareStatement(CREATE_LAB_TEST_LIST,
					PreparedStatement.RETURN_GENERATED_KEYS);

			st.setInt(1, labTestRecordId);
			st.setInt(2, labTestId);

			int affectedRows = st.executeUpdate();

			// Don't attempt to get keys if there is no affected row
			if (affectedRows > 0) {
				ResultSet keys = st.getGeneratedKeys();

				// Get key, return newUser
				if (keys.next()) {
					int listId = keys.getInt(1);

					list = new LabTestList();
					list.setLabTestId(labTestId);
					list.setLabTestListId(listId);
					list.setLabTestRecordId(labTestRecordId);

					if (keys != null) {
						keys.close();
					}
				}
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return list;
	}

	public void updateLabTestList(int labTestRecordId, int labTestId,
			int labTestListId) {
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(UPDATE_LAB_TEST_LIST);
			st.setInt(1, labTestRecordId);
			st.setInt(2, labTestId);
			st.setInt(3, labTestListId);

			st.executeUpdate();

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public ArrayList<LabTestList> getLabTestListOfLabTestRecord(
			int labTestRecordId) {
		ArrayList<LabTestList> arr = new ArrayList<LabTestList>();
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn
					.prepareStatement(GET_LAB_TEST_LIST_FROM_ID);

			st.setInt(1, labTestRecordId);

			ResultSet rs = st.executeQuery();

			// Found result!
			while (rs.next()) {
				LabTestList list = new LabTestList();
				list.setLabTestId(rs.getInt("labTestId"));
				list.setLabTestListId(rs.getInt("labTestListId"));
				list.setLabTestRecordId(rs.getInt("labTestRecordId"));

				arr.add(list);
			}

			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return arr;
	}

	public ArrayList<LabTestList> getAllLabTestCategory() {
		ArrayList<LabTestList> catList = new ArrayList<LabTestList>();
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn.prepareStatement(GET_ALL_LAB_TEST_LIST);

			ResultSet rs = st.executeQuery();

			// Found result!
			if (rs.next()) {
				LabTestList list = new LabTestList();
				list.setLabTestId(rs.getInt("labTestId"));
				list.setLabTestListId(rs.getInt("labTestListId"));
				list.setLabTestRecordId(rs.getInt("labTestRecordId"));

				catList.add(list);
			}

			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return catList;
	}

}
